﻿using BCC.Common;
using BCC.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Transactions;

namespace DBOperation
{
    /// <summary>
    /// 数据库通用操作
    /// </summary>
    public class DBCommon
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private static readonly string BCCConnectionString = Utils.Decrypt(ConfigurationManager.AppSettings["BCCConnectionString"].Trim(), "SrSm", Encoding.Unicode);

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <returns></returns>
        public static int Add<T>(T t) where T : class, new()
        {
            try
            {
                StringBuilder stringBuilderName = new StringBuilder();

                StringBuilder stringBuilderValue = new StringBuilder();

                using (TransactionScope tran = new TransactionScope())
                {
                    using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            conn.Open();

                            cmd.Connection = conn;

                            Type type = t.GetType();

                            string tableName = $"t_{type.Name}";

                            foreach (var property in type.GetProperties())
                            {
                                if (!property.IsDefined(typeof(KeyAttribute), true))
                                {
                                    cmd.Parameters.Add(new MySqlParameter() { ParameterName = $"@{property.Name}", Value = property.GetValue(t) });

                                    stringBuilderName.Append($"{property.Name},");

                                    stringBuilderValue.Append($"@{property.Name},");
                                }
                            }

                            object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                            tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                            cmd.CommandType = CommandType.Text;

                            cmd.CommandText = $"insert into {tableName} ({stringBuilderName.ToString().Trim(',')}) values({stringBuilderValue.ToString().Trim(',')});";

                            cmd.ExecuteNonQuery();

                            int id = Convert.ToInt32(cmd.LastInsertedId);

                            conn.Close();

                            tran.Complete();

                            return id;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"Add_{t.GetType().Name}：{ex.Message}", ex);

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 新增_存储过程
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <param storedProcedureName="t">存储过程名称</param>
        /// <returns></returns>
        public static int Add_StoredProcedure<T>(T t, string storedProcedureName) where T : class, new()
        {
            try
            {
                using (TransactionScope tran = new TransactionScope())
                {
                    using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            Type type = t.GetType();

                            conn.Open();

                            cmd.Connection = conn;

                            cmd.CommandText = storedProcedureName;

                            cmd.CommandType = CommandType.StoredProcedure;

                            foreach (var property in type.GetProperties())
                            {
                                if (!property.IsDefined(typeof(KeyAttribute), true))
                                {
                                    cmd.Parameters.Add(new MySqlParameter() { ParameterName = $"_{property.Name}", Value = property.GetValue(t) });
                                }
                            }

                            cmd.Parameters.Add(new MySqlParameter() { ParameterName = "_Result", Direction = ParameterDirection.Output });

                            cmd.ExecuteNonQuery();

                            //int result = Convert.ToInt32(cmd.Parameters["_Result"].Value);

                            int id = Convert.ToInt32(cmd.LastInsertedId);

                            conn.Close();

                            tran.Complete();

                            return id;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"Add_StoredProcedure_{t.GetType().Name}：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <returns></returns>
        public static bool Update<T>(T t) where T : class, new()
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();

                using (TransactionScope tran = new TransactionScope())
                {
                    using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            conn.Open();

                            cmd.Connection = conn;

                            Type type = t.GetType();

                            string tableName = $"t_{type.Name}";

                            string primaryKey = "id";

                            object primaryKeyValue = string.Empty;

                            foreach (var property in type.GetProperties())
                            {
                                if (!property.IsDefined(typeof(KeyAttribute), true))
                                {
                                    cmd.Parameters.Add(new MySqlParameter() { ParameterName = $"@{property.Name}", Value = property.GetValue(t) });

                                    stringBuilder.Append($"{property.Name}=@{property.Name},");
                                }
                                else
                                {
                                    primaryKey = property.Name;

                                    primaryKeyValue = property.GetValue(t);
                                }
                            }

                            object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                            tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                            cmd.CommandText = $"update {tableName} set {stringBuilder.ToString().Trim(',')} where {primaryKey} = {primaryKeyValue};";

                            cmd.CommandType = CommandType.Text;

                            bool b = Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;

                            conn.Close();

                            tran.Complete();

                            return b;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"Update_{t.GetType().Name}：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <returns></returns>
        public static bool Delete<T>(int id) where T : class, new()
        {
            try
            {
                using (TransactionScope tran = new TransactionScope())
                {
                    using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            conn.Open();

                            cmd.Connection = conn;

                            Type type = new T().GetType();

                            string tableName = $"t_{type.Name}";

                            string primaryKey = "id";

                            foreach (var property in type.GetProperties())
                            {
                                if (property.IsDefined(typeof(KeyAttribute), true))
                                {
                                    primaryKey = property.Name;
                                }
                            }

                            object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                            tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                            cmd.CommandText = $"delete from {tableName} where {primaryKey} = {id}";

                            cmd.CommandType = CommandType.Text;

                            bool b = Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;

                            conn.Close();

                            tran.Complete();

                            return b;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"Delete：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <returns></returns>
        public static T GetModel<T>(int id) where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        string primaryKey = "id";

                        foreach (var property in type.GetProperties())
                        {
                            if (property.IsDefined(typeof(KeyAttribute), true))
                            {
                                primaryKey = property.Name;
                            }
                        }

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select * from {tableName} where {primaryKey} = {id};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            conn.Close();

                            if (dt == null || dt.Rows.Count <= 0) return null;

                            return Utils.ObjectToList<List<T>>(dt)?[0];
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"GetModel：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="where">条件</param>
        /// <param name="select">查询字段</param>
        /// <returns></returns>
        public static T GetModel<T>(string where, string select = "*") where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select {select} from {tableName} where 1 = 1 {where};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);
                            dbdatareader.Close();
                            conn.Close();

                            if (dt == null || dt.Rows.Count <= 0) return null;

                            return Utils.ObjectToList<List<T>>(dt)?[0];
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"GetModel：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据Id判断数据存在
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <returns></returns>
        public static bool IsExist<T>(int id) where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        string primaryKey = "id";

                        foreach (var property in type.GetProperties())
                        {
                            if (property.IsDefined(typeof(KeyAttribute), true))
                            {
                                primaryKey = property.Name;
                            }
                        }

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select count(1) from {tableName} where {primaryKey} = {id};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            conn.Close();

                            return Convert.ToInt32(dt.Rows[0]["count(1)"]) > 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"IsExist：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据sql条件判断数据存在
        /// </summary>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static bool IsExist<T>(string where) where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select count(1) from {tableName} where 1 = 1 {where};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            dbdatareader.Close();

                            conn.Close();

                            return Convert.ToInt32(dt.Rows[0]["count(1)"]) > 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"IsExist：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取实体数据表
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="select">查询字段</param>
        /// <returns></returns>
        public static DataTable GetModelDataTable(string sqlStr)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        cmd.CommandText = sqlStr;

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            conn.Close();

                            return dt;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"GetModelDataTable：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取实体集合
        /// </summary>
        /// <param name="where">条件</param>
        /// <param name="select">查询字段</param>
        /// <returns></returns>
        public static List<T> GetModelList<T>(string where = "", string select = "*") where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select {select} from {tableName} where 1 = 1 {where};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            conn.Close();

                            if (dt == null || dt.Rows.Count <= 0) return null;

                            return Utils.ObjectToList<List<T>>(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"GetModelList：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取实体分页数据集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="page">页数</param>
        /// <param name="pageNumber">每页条数</param>
        /// <param name="select">查询字段</param>
        /// <param name="where">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public static List<T> GetModelPagingDataList<T>(int page = 1, int pageNumber = 10, string select = "*", string where = "", string orderBy = "CreateTime DESC") where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BCCConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();

                        cmd.Connection = conn;

                        Type type = new T().GetType();

                        string tableName = $"t_{type.Name}";

                        orderBy = !string.IsNullOrEmpty(orderBy) ? $"ORDER BY {orderBy}" : "";

                        object tableNameAttribute = type.GetCustomAttributes(true).ToList().First(x => x.GetType().Name.Equals("TableNameAttribute"));

                        tableName = tableNameAttribute != null ? ((TableNameAttribute)tableNameAttribute).Tablename : tableName;

                        cmd.CommandText = $"select {select} from {tableName} where 1 = 1 {where} {orderBy} LIMIT {(page - 1) * pageNumber},{pageNumber};";

                        cmd.CommandType = CommandType.Text;

                        using (MySqlDataReader dbdatareader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();

                            dt.Load(dbdatareader);

                            conn.Close();

                            if (dt == null || dt.Rows.Count <= 0) return null;

                            return Utils.ObjectToList<List<T>>(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //LogTool.ExceptionLog(ex, $"GetModelPagingDataList：{ex.Message}");

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取数据总条数
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public static int GetDataTotal(string tableName, string where = "")
        {
            string sqlStr = $"select COUNT(1) AS Total from {tableName} where 1=1 {where}";

            DataTable dt = GetModelDataTable(sqlStr);

            return Convert.ToInt32(dt?.Rows[0]["Total"]);
        }
    }
}
